from IPython.display import HTML
HTML('''<script>
code_show=true;
function code_toggle() {
if (code_show){
$('div.input').hide();
} else {
$('div.input').show();
}
code_show = !code_show
}
$( document ).ready(code_toggle);
</script>
<form action="javascript:code_toggle()"><input type="submit" value="Click here to toggle on/off the raw code."></form>''')
import pandas as pd
expense = pd.read_csv(r"C:\Users\yilin\OneDrive\Desktop\red cross\charitydata-119219814RR0001-expenses.csv", skiprows=1, index_col=0)
total_expense = expense.iloc[-1,::-1].to_frame()
total_expense
| Total expenditures ($) | |
|---|---|
| 2003 | 191334231.0 |
| 2004 | 195510000.0 |
| 2005 | 0.0 |
| 2006 | 283949891.0 |
| 2007 | 257229755.0 |
| 2008 | 299197026.0 |
| 2009 | 415271172.0 |
| 2010 | 366804504.0 |
| 2011 | 419674677.0 |
| 2012 | 386445763.0 |
| 2013 | 306388801.0 |
| 2014 | 282271763.0 |
| 2015 | 310099360.0 |
| 2016 | 313153540.0 |
| 2017 | 516908994 |
| 2018 | 457956426 |
| 2019 | 381758320 |
| 2020 | 347602783 |
| 2021 | 553402595 |
# change data type
total_expense = total_expense['Total expenditures ($)'].astype('int64')
revenue = pd.read_csv(r"C:\Users\yilin\OneDrive\Desktop\red cross\charitydata-119219814RR0001-revenue.csv", skiprows=1, index_col=0)
total_revenue = revenue.iloc[-1,::-1].to_frame()
total_revenue
| Total revenue ($) | |
|---|---|
| 2003 | 191388361.0 |
| 2004 | 197908000.0 |
| 2005 | 485211918.0 |
| 2006 | 362879651.0 |
| 2007 | 230604119.0 |
| 2008 | 253196921.0 |
| 2009 | 325330082.0 |
| 2010 | 449211535.0 |
| 2011 | 403926824.0 |
| 2012 | 349698551.0 |
| 2013 | 255564842.0 |
| 2014 | 328775664.0 |
| 2015 | 275720312.0 |
| 2016 | 314648445.0 |
| 2017 | 612082298 |
| 2018 | 476646615 |
| 2019 | 340074588 |
| 2020 | 336899823 |
| 2021 | 563961036 |
# change data type
total_revenue = total_revenue['Total revenue ($)'].astype('int64')
# data visualization
# define colors
GRAY1, GRAY2, GRAY3 = '#231F20', '#414040', '#555655'
GRAY4, GRAY5, GRAY6 = '#646369', '#76787B', '#828282'
GRAY7, GRAY8, GRAY9 = '#929497', '#A6A6A5', '#BFBEBE'
BLUE1, BLUE2, BLUE3, BLUE4 = '#174A7E', '#4A81BF', '#94B2D7', '#94AFC5'
RED1, RED2 = '#C3514E', '#E6BAB7'
GREEN1, GREEN2 = '#0C8040', '#9ABB59'
ORANGE1 = '#F79747'
import plotly.graph_objects as go
import plotly.express as px
# line plot
fig = go.Figure(data=go.Scatter(x=total_expense.index.values[-5:],
y=total_expense.values[-5:],
mode='lines',
line=dict(color=ORANGE1, width=3),
name='Total Expense'))
fig.add_trace(
go.Scatter(x=total_revenue.index.values[-5:],
y=total_revenue.values[-5:],
mode='lines',
line=dict(color=BLUE1, width=3),
name='Total Revenue'))
# scatter plot
fig.add_trace(
go.Scatter(x=total_revenue.index.values[-3:],
y=total_revenue.values[-3:],
mode='markers+text',
marker=dict(color=BLUE1, size=12),
name='Total Revenue'))
fig.add_trace(
go.Scatter(x=total_expense.index.values[-3:],
y=total_expense.values[-3:],
mode='markers+text',
marker=dict(color=ORANGE1, size=12),
name='Total Expense'))
# vertical line
fig.add_vline(x=2, line_width=2.5, line_color=GRAY1)
# yaxes
fig.update_yaxes(range=(0, 700000000),
title_text='$',
title_standoff=15,
title_font=dict(size=16, family='Arial'),
tickfont=dict(size=16, family='Arial'),
automargin=True)
# annotation
annotation = []
# label lines directly
annotation.append(
dict(xref='paper',
yref='paper',
x=0,
y=0.95,
text='Total Revenue',
font=dict(family='Arial', size=15, color=BLUE1),
showarrow=False))
annotation.append(
dict(
xref='paper',
yref='paper',
x=-0.1,
y=-0.27,
xanchor='left',
yanchor='bottom',
text='Data source: red cross website',
font=dict(family='Arial', size=11.5, color=GRAY3),
showarrow=False,
align='left'))
annotation.append(
dict(xref='paper',
yref='paper',
x=0,
y=0.65,
text='Total Expense',
font=dict(family='Arial', size=15, color=ORANGE1),
showarrow=False))
annotation.append(
dict(xref='paper',
yref='paper',
x=1.12,
y=1.12,
text='<b>From 2019 to 2020, our expense are higher than revenue</b><br>We nearly maintain a balance between revenue and expense in 2021',
font=dict(family='Arial', size=13, color=GRAY5),
align='left',
showarrow=False))
annotation.append(
dict(x=-0.1,
y=1.45,
xref='paper',
yref='paper',
text='<b>Please cut down on our expenses</b>',
font=dict(size=30, color=GRAY3),
showarrow=False))
annotation.append(
dict(x=-0.1,
y=1.32,
xref='paper',
yref='paper',
text='to balance the budget',
font=dict(size=20, color=GRAY7),
showarrow=False))
annotation.append(
dict(x=-0.1,
y=1.2,
xref='paper',
yref='paper',
text='total revenue vs total expense over time',
font=dict(family='Arial', size=18, color=GRAY2),
showarrow=False))
fig.update_layout(template='simple_white',
font=dict(family='Arial', size=15, color='#646369'),
showlegend=False,
margin=dict(t=150, r=100),
height= 500,
width=800,
annotations = annotation)
fig.show()
fig.write_html("total Revenue VS Total Expense.html")
From above line chart, we can clearly see the trend of total revenue and total expense over time in the recent 5 years.